package org.alex.samples.dao;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.alex.samples.model.ExamScore;
import org.alex.samples.model.ExamScoreVO;
import org.alex.samples.model.ExamSubject;
import org.alex.samples.model.UserJDBC;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class ExamScoreDAOImpl extends BaseHibernateDAO<ExamScore> implements IExamScoreDAO {

	@Autowired
	public IExamSubjectDAO examSubjectDAO;
	
	@Autowired
	public IUserJdbcDAO examUserDAO;
	
	
	//根据用户Id和科目Id读取成绩
	@Override
	public ExamScore getScore(String userId, Integer subjectId) {
		System.out.println("getScore.userId"+userId);
		System.out.println("getScore.subjectId"+subjectId);
		String hql=" FROM ExamScore es WHERE es.userId= :userId AND es.subjectId= :subjectId";
		return (ExamScore) this.createQuery(hql)
					.setString("userId",userId)
					.setInteger("subjectId",subjectId)
					.uniqueResult();
	}


//通过Id查询成绩
	@Override
	public ExamScoreVO getScoreById(Integer scoreId) {
		StringBuilder hql=new StringBuilder();
		hql.append(" select user.userName as userName,sub.name as subjectName,es.examTime as examTime,es.score as score");
		hql.append(" from ExamScore es,UserJDBC user,ExamSubject sub");
		hql.append(" where es.userId = user.userId ");
		hql.append(" and es.subjectId = sub.subId ");
		hql.append(" and es.id = :scoreId ");
		
		return (ExamScoreVO) this.createQuery(hql.toString())
						.setResultTransformer(Transformers.aliasToBean(ExamScoreVO.class))
						.setInteger("scoreId", scoreId)
						.uniqueResult();
	}

	//查询全部成绩
	@SuppressWarnings("unchecked")
	@Override
	public List<ExamScoreVO> getAllScore(Map<String, String> conditionMap) {
		StringBuilder hql=new StringBuilder();
		hql.append(" select es.id as id,user.userId as userId,user.userName as userName,sub.subId as subjectId,sub.name as subjectName");
		hql.append(" from ExamScore es,UserJDBC user,ExamSubject sub");
		hql.append(" where es.userId = user.userId ");
		hql.append(" and es.subjectId = sub.subId ");
		
		//当条件为空时，相当于查询全部
		if(conditionMap != null){
			Iterator<Entry<String,String>> it=conditionMap.entrySet().iterator();
				while(it.hasNext()){
					Entry<String,String> currentCondition=it.next();
					//当有指定类型的查询条件时，按条件查询
					if("subjectName".equals(currentCondition.getKey())){
						hql.append(" and sub.name like '%" + currentCondition.getValue() + "%' ");
					}else if("userId".equals(currentCondition.getKey())){
						hql.append(" and user.userId like '%" + currentCondition.getValue() + "%' ");
					}else if("userName".equals(currentCondition.getKey())){
						hql.append(" and user.userName like '%" + currentCondition.getValue() + "%' ");
					}else if("examTime".equals(currentCondition.getKey())){
						if(currentCondition.getValue() != null){
							hql.append(" and DATE_FORMAT(es.examTime,'%Y-%m-%d %H:%i') like '%" + currentCondition.getValue() + "%' ");
						}
					}
				}
	        }
		return this.createQuery(hql.toString()).setResultTransformer(Transformers.aliasToBean(ExamScoreVO.class)).list();
	}

//修改成绩
	@Override
	public void editScore(String userId, Integer subjectId, Integer score) {
		StringBuilder hql = new StringBuilder();
		hql.append(" UPDATE ExamScore es SET es.score = :score ");
		hql.append(" WHERE es.userId = :userId AND es.subjectId = :subjectId ");
		this.createQuery(hql.toString())
				.setInteger("score", score)
				.setString("userId", userId)
				.setInteger("subjectId", subjectId)
				.executeUpdate();
		
	}


	//查询全部成绩
	@Override
	public List<ExamScore> FindAllScore() {
		return this.find(" FROM ExamScore AS es WHERE es.score > 0");
	}


	//根据科目查询成绩
	@SuppressWarnings("unchecked")
	@Override
	public List<ExamScore> getScoreBySub(String query) {
		ExamSubject examSubject=examSubjectDAO.getSubjectByName(query);
		StringBuilder hql=new StringBuilder();
		hql.append(" FROM ExamScore es");
		hql.append(" WHERE es.subjectId= :subjectId");
		return this.createQuery(hql.toString())
				.setInteger("subjectId", examSubject.getSubId())
				.list();
	}


	//根据学号查询成绩
	@SuppressWarnings("unchecked")
	@Override
	public List<ExamScore> getScoreByUserId(String query) {
		StringBuilder hql = new StringBuilder();
		hql.append(" FROM ExamScore es ");
		hql.append(" WHERE es.userId= :userId");
		return this.createQuery(hql.toString())
				.setString("userId", query)
				.list();
	}


	//根据学生姓名查询成绩
	@SuppressWarnings("unchecked")
	@Override
	public List<ExamScore> getScoreByUserName(String query) {
		UserJDBC examUser=examUserDAO.getUserByName(query);
		StringBuilder hql=new StringBuilder();
		hql.append(" FROM ExamScore es");
		hql.append(" WHERE es.userId= :userId");
		return this.createQuery(hql.toString())
				.setString("userId", examUser.getUserId())
				.list();
	}


	//根据日期查询成绩
	@SuppressWarnings("unchecked")
	@Override
	public List<ExamScore> getScoreByDate(String[] date) {
		StringBuilder hql = new StringBuilder();
		hql.append(" FROM ExamScore es ");
		hql.append(" WHERE DATE(es.examTime)>= :D1 AND DATE(es.examTime)<= :D2 ");
		return this.createQuery(hql.toString())
				.setString("D1", date[0])
				.setString("D2", date[1])
				.list();
	}

//修改成绩
	@Override
	public void updateScore(Integer id, Double score) {
		ExamScore examScore = this.get(id);
		examScore.setScore(score);
	}

}
